﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

using System.Xml.Linq;
using System.Data.SqlClient;

namespace imaginecup.Web
{
    /// <summary>
    /// Summary description for DataProvider
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class DataProvider : System.Web.Services.WebService
    {
        private string recordTable = "PP_Data";
        private string pollutedTable = "PP_Layer";
        private string dbname = "PaintedPlanet";
        private string positionTable = "PP_Position";
        private string hotTable = "PP_HotPoint";

        /// <summary>
        /// 将固定格式的字符串转换为时间串
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        private DateTime stringToDateTime(string s)
        {
            int index1 = s.IndexOf('-');
            int index2 = s.IndexOf('-', index1 + 1);

            int index3 = s.IndexOf(' ', index2 + 1);
            int index4 = s.IndexOf(':', index3 + 1);
            int index5 = s.IndexOf(':', index4 + 1);

            int year = Convert.ToInt32(s.Substring(0, index1));
            int month = Convert.ToInt32(s.Substring(index1 + 1, index2 - index1 - 1));
            int day = Convert.ToInt32(s.Substring(index2 + 1, index3 - index2 - 1));

            int hour = Convert.ToInt32(s.Substring(index3 + 1, index4 - index3 - 1));
            int min = Convert.ToInt32(s.Substring(index4 + 1, index5 - index4 - 1));
            int sec = Convert.ToInt32(s.Substring(index5 + 1, s.Length - index5 - 1));

            return new DateTime(year, month, day, hour, min, sec) ;
        }

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

        [WebMethod]
        public List<CMessage> getMessage(double east,double west,double north,double south)
        {
            List<CMessage> messageresult = new List<CMessage>();

            //测试访问数据库
            string DataServer = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=newtest;User ID=sa;Password=123456";
            SqlConnection con = new SqlConnection(DataServer);


            SqlCommand com = new SqlCommand();
            com.CommandType = System.Data.CommandType.Text;
            com.Connection = con;
            com.CommandText = "select * from " + recordTable + " where longtitude>" + west + " and longtitude<" + east + " and latitude<" + north + " and latitude>" + south + ";";

            con.Open();
            con.ChangeDatabase(dbname);
            SqlDataReader messageReader = com.ExecuteReader();

            while (messageReader.Read())
            {
                CMessage messageitem = new CMessage();
                messageitem.Comment = messageReader["comments"].ToString();
                messageitem.Latitude = Convert.ToDouble(messageReader["latitude"].ToString());
                messageitem.Longtitude = Convert.ToDouble(messageReader["longtitude"].ToString());
                messageitem.Name = messageReader["ip"].ToString();
                //需要修改
                messageitem.PostTime = stringToDateTime(messageReader["date"].ToString());

                messageresult.Add(messageitem);
            }
            messageReader.Close();

            con.Close();
            //~end of sql server

            return messageresult;
            //return getMessageTest();
        }

        /// <summary>
        /// 供留言测试的xml数据提供模块
        /// </summary>
        /// <returns></returns>
        private List<CMessage> getMessageTest()
        {
            string filename = HttpContext.Current.Server.MapPath("message.xml");

            List<CMessage> mylist = new List<CMessage>();

            try
            {
                XDocument doc = XDocument.Load(filename);
                var items = doc.Descendants("messageitem").Select(p => new
                {
                    MID = Convert.ToInt32(p.Element("mid").Value),
                    USERNAME = p.Element("username").Value,
                    POSTDATE = p.Element("postdate").Value,
                    MESSAGE = p.Element("message").Value,
                    REPLYTOMID = Convert.ToInt32(p.Element("replytomid").Value),
                    LONGTITUDE = Convert.ToDouble(p.Element("longtitude").Value),
                    LATITUDE = Convert.ToDouble(p.Element("latitude").Value)
                });


                foreach (var item in items)
                {
                    CMessage tmpmessage = new CMessage();
                    tmpmessage.Comment = item.MESSAGE;
                    tmpmessage.Latitude = item.LATITUDE;
                    tmpmessage.Longtitude = item.LONGTITUDE;
                    tmpmessage.Name = item.USERNAME;
                    tmpmessage.PostTime = stringToDateTime(item.POSTDATE);
                    mylist.Add(tmpmessage);
                }
            }
            catch
            {

            }
            finally
            {

            }

            return mylist;
        }

        [WebMethod]
        public List<CPhoto> getPhoto(double east,double west,double north,double south)
        {
            List<CPhoto> photoresult = new List<CPhoto>();
            //测试访问数据库
            string DataServer = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=newtest;User ID=sa;Password=123456";
            SqlConnection con = new SqlConnection(DataServer);


            SqlCommand com = new SqlCommand();
            com.CommandType = System.Data.CommandType.Text;
            com.Connection = con;
            com.CommandText = "select * from "+recordTable +" where longtitude>"+west+" and longtitude<"+east+" and latitude<"+north+" and latitude>"+south+";";

            con.Open();
            con.ChangeDatabase(dbname);
            SqlDataReader photoReader = com.ExecuteReader();

            while (photoReader.Read())
            {
                CPhoto photoitem = new CPhoto();
                photoitem.FileSuffix = photoReader["images"].ToString();
                photoitem.Latitude = Convert.ToDouble(photoReader["latitude"].ToString());
                photoitem.Longtitude = Convert.ToDouble(photoReader["longtitude"].ToString());
                photoitem.Name = photoReader["ip"].ToString();
                //需要进一步处理
                photoitem.PostTime = stringToDateTime(photoReader["date"].ToString());
                if( photoitem.FileSuffix != "")
                    photoresult.Add(photoitem);
            }
            photoReader.Close();

            con.Close();
            //~end of sql server

            return photoresult;
            //return getPhotoTest();
        }

        /// <summary>
        /// 供测试图片模块使用
        /// </summary>
        /// <returns></returns>
        private List<CPhoto> getPhotoTest()
        {
            List<CPhoto> list = new List<CPhoto>();
            string filename = HttpContext.Current.Server.MapPath("photo.xml");

            try
            {
                XDocument doc = XDocument.Load(filename);
                var items = doc.Descendants("photorecord").Select(p => new
                {
                    PHOTOID = Convert.ToInt32(p.Element("photoid").Value),
                    FILENAME = p.Element("filename").Value,
                    USERNAME = p.Element("username").Value,
                    UPLOADDATE = p.Element("uploadtime").Value,
                    DESCRIPTION = p.Element("description").Value,
                    LONGTITUDE = Convert.ToDouble(p.Element("longtitude").Value),
                    LATITUDE = Convert.ToDouble(p.Element("latitude").Value)
                });

                foreach (var item in items)
                {
                    CPhoto tmprecord = new CPhoto();
                    tmprecord.FileSuffix = item.FILENAME;
                    tmprecord.Latitude = item.LATITUDE;
                    tmprecord.Longtitude = item.LONGTITUDE;
                    tmprecord.Name = item.USERNAME;
                    tmprecord.PostTime = stringToDateTime(item.UPLOADDATE);

                    list.Add(tmprecord);
                }
            }
            catch
            {
            }
            finally
            {
            }

            return list;
        }

        //[WebMethod]
        //public List<CRecord> getRecord(double east, double west, double north, double south)
        //{
        //    return null;
        //}

        [WebMethod]
        public List<CPollutedRecord> getPollutedRecord(double east, double west, double north, double south)
        {
            List<CPollutedRecord> pollutedresult = new List<CPollutedRecord>();
            try
            {
                //测试访问数据库
                string DataServer = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=newtest;User ID=sa;Password=123456";
                SqlConnection con = new SqlConnection(DataServer);


                SqlCommand com = new SqlCommand();
                com.CommandType = System.Data.CommandType.Text;
                com.Connection = con;
                com.CommandText = "select * from " + recordTable + " where longtitude>" + west + " and longtitude<" + east + " and latitude<" + north + " and latitude>" + south + ";";

                con.Open();
                con.ChangeDatabase(dbname);
                SqlDataReader pollutedReader = com.ExecuteReader();

                while (pollutedReader.Read())
                {
                    CPollutedRecord pr = new CPollutedRecord();
                    pr.Comments = pollutedReader["comments"].ToString();
                    pr.Images = pollutedReader["images"].ToString();
                    pr.Latitude = Convert.ToDouble(pollutedReader["latitude"].ToString());
                    pr.Longtitude = Convert.ToDouble(pollutedReader["longtitude"].ToString());
                    pr.Name = pollutedReader["ip"].ToString();
                    pr.Rank_0 = Convert.ToDouble(pollutedReader["rank_0"].ToString());
                    pr.Rank_1 = Convert.ToDouble(pollutedReader["rank_1"].ToString());
                    pr.Rank_2 = Convert.ToDouble(pollutedReader["rank_2"].ToString());
                    pr.Rank_3 = Convert.ToDouble(pollutedReader["rank_3"].ToString());
                    pr.Rank_4 = Convert.ToDouble(pollutedReader["rank_4"].ToString());

                    //需要修改
                    pr.PostTime = stringToDateTime(pollutedReader["date"].ToString());

                    pollutedresult.Add(pr);
                }

                con.Close();
                //~end of sql server
            }
            catch (Exception e)
            {
            }
            return pollutedresult;
            //return getPhotoTest();
        }

        [WebMethod]
        public List<CPollutedUnit> getPolluteUnit(double east, double west, double north, double south,int level,int type)
        {

            List<CPollutedUnit> pollutedunit = new List<CPollutedUnit>();
            try
            {
                //测试访问数据库
                string DataServer = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=newtest;User ID=sa;Password=123456";
                SqlConnection con = new SqlConnection(DataServer);


                SqlCommand com = new SqlCommand();
                com.CommandType = System.Data.CommandType.Text;
                com.Connection = con;
                com.CommandText = "select * from " + pollutedTable + " where longtitude>" + west + " and longtitude<" + east + " and latitude<" + north + " and latitude>" + south + " and layer=" + level +";";

                con.Open();
                con.ChangeDatabase(dbname);
                SqlDataReader pollutedReader = com.ExecuteReader();

                while (pollutedReader.Read())
                {
                    CPollutedUnit pu = new CPollutedUnit();
                    pu.Level = Convert.ToInt32(pollutedReader["layer"].ToString());
                    pu.NorthLatitude = Convert.ToDouble(pollutedReader["latitude"].ToString());
                    pu.WestLongtitude = Convert.ToDouble(pollutedReader["longtitude"].ToString());
                    
                    pu.Count = Convert.ToInt32(pollutedReader["count"].ToString());
                    pu.Rank_0 = Convert.ToDouble(pollutedReader["rank_0"].ToString());
                    pu.Rank_1 = Convert.ToDouble(pollutedReader["rank_1"].ToString());
                    pu.Rank_2 = Convert.ToDouble(pollutedReader["rank_2"].ToString());
                    pu.Rank_3 = Convert.ToDouble(pollutedReader["rank_3"].ToString());
                    pu.Rank_4 = Convert.ToDouble(pollutedReader["rank_4"].ToString());
                    pollutedunit.Add(pu);
                }
                pollutedReader.Close();

                con.Close();
                //~end of sql server
            }
            catch(Exception e)
            {
            }
            finally
            {
            }

            return pollutedunit;
            //return getPhotoTest();
        }

        [WebMethod]
        public bool insertRecord(CPollutedRecord r)
        {
            bool result = true;
            try
            {
                //测试访问数据库
                string DataServer = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=newtest;User ID=sa;Password=123456";
                SqlConnection con = new SqlConnection(DataServer);
                con.Open();
                con.ChangeDatabase(dbname);

                SqlCommand com = new SqlCommand();
                com.CommandType = System.Data.CommandType.Text;
                com.Connection = con;
                com.CommandText = "insert into " + recordTable + " (longtitude,latitude,ip,date,comments,images,rank_0,rank_1,rank_2,rank_3,rank_4) values('" + r.Longtitude + "','"
                                                                                                                                                    + r.Latitude + "','"
                                                                                                                                                    + r.Name + "','"
                                                                                                                                                    + r.PostTime + "','"
                                                                                                                                                    + r.Comments + "','"
                                                                                                                                                    + r.Images + "','"
                                                                                                                                                    + r.Rank_0 + "','"
                                                                                                                                                    + r.Rank_1 + "','"
                                                                                                                                                    + r.Rank_2 + "','"
                                                                                                                                                    + r.Rank_3 + "','"
                                                                                                                                                    + r.Rank_4 + "');";
                
                SqlDataReader dr =  com.ExecuteReader();
                dr.Close();

                //还需要更新每一级level的值

                for (int level = 0; level < 17; level++)
                {
                    double length = getLevelLength(level);
                    double tmplongtitude = -180 + ((int)((r.Longtitude - (-180)) / length)) * length;
                    double tmplatitude = 90 - ((int)((90 - r.Latitude) / length)) * length;
                    //怎样定位此表项。。。
                    //首先通过level和坐标值进行定位

                    //更新当前数据
                    SqlCommand getrid = new SqlCommand();
                    SqlCommand update = new SqlCommand();

                    //首先定位此表项
                    getrid.CommandType = System.Data.CommandType.Text;
                    getrid.Connection = con;
                    getrid.CommandText= "select * from " + pollutedTable + " where longtitude=" + tmplongtitude + " and latitude=" + r.Latitude + ";";

                    SqlDataReader getridreader = getrid.ExecuteReader();
                    if (getridreader.HasRows)
                    {

                        int rid = Convert.ToInt32(getridreader["id"].ToString());
                        int orgcount = Convert.ToInt32(getridreader["count"].ToString());
                        double orgvalue0 = Convert.ToDouble(getridreader["rank_0"].ToString());
                        double orgvalue1 = Convert.ToDouble(getridreader["rank_1"].ToString());
                        double orgvalue2 = Convert.ToDouble(getridreader["rank_2"].ToString());
                        double orgvalue3 = Convert.ToDouble(getridreader["rank_3"].ToString());
                        double orgvalue4 = Convert.ToDouble(getridreader["rank_4"].ToString());
                        getridreader.Close();

                        int count = orgcount + 1;
                        double value0 = (orgvalue0 * orgvalue0 + r.Rank_0) / count;
                        double value1 = (orgvalue1 * orgvalue1 + r.Rank_1) / count;
                        double value2 = (orgvalue2 * orgvalue2 + r.Rank_2) / count;
                        double value3 = (orgvalue3 * orgvalue3 + r.Rank_3) / count;
                        double value4 = (orgvalue4 * orgvalue4 + r.Rank_4) / count;

                        //然后更新表项内的值
                        string updatecommand = "update " + pollutedTable + " set rank_0=" + value0 +
                                                                        ",rank_1=" + value1 +
                                                                        ",rank_2=" + value2 +
                                                                        ",rank_3=" + value3 +
                                                                        ",rank_4=" + value4 +
                                                                        "where id=" + rid + " and layer=" + level + ";";
                        update.CommandType = System.Data.CommandType.Text;
                        update.Connection = con;
                        update.CommandText = updatecommand;
                        SqlDataReader udr = update.ExecuteReader();
                        udr.Close();
                    }
                    else
                    {
                        getridreader.Close();

                        string insertcommand = "insert " + pollutedTable + " (layer,rank_0,rank_1,rank_2,rank_3,rank_4,longtitude,latitude,count) values('" +
                                                                                                                                                        level + "','" +
                                                                                                                                                        r.Rank_0 + "','" +
                                                                                                                                                        r.Rank_1 + "','" +
                                                                                                                                                        r.Rank_2 + "','" +
                                                                                                                                                        r.Rank_3 + "','" +
                                                                                                                                                        r.Rank_4 + "','" +
                                                                                                                                                        tmplongtitude + "','" +
                                                                                                                                                        tmplatitude + "','" +
                                                                                                                                                        "1" + "');";
                        update.CommandType = System.Data.CommandType.Text;
                        update.Connection = con;
                        update.CommandText = insertcommand;
                        SqlDataReader udr = update.ExecuteReader();
                        udr.Close();

                    }
                }

                con.Close();
                //~end of sql server
            }
            catch (Exception e)
            {
                result = false;
            }
            finally
            {
            }

            return result;
        }

        //这个函数还需要重新考虑
        private double getLevelLength(int l)
        {
            return 0.17578125 * 4 * 8 / (Math.Pow(2, l)) ;
        }

        [WebMethod]
        public List<CPosition> getPositions(string key)
        {
            List<CPosition> result = new List<CPosition>();

            try
            {
                string DataServer = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=newtest;User ID=sa;Password=123456";
                SqlConnection con = new SqlConnection(DataServer);

                SqlCommand com = new SqlCommand();
                com.CommandType = System.Data.CommandType.Text;
                com.Connection = con;
                com.CommandText = "select * from " + positionTable + " where pname like '%"+key+"%'";

                con.Open();
                con.ChangeDatabase(dbname);
                SqlDataReader positionReader = com.ExecuteReader();

                while (positionReader.Read())
                {
                    CPosition p = new CPosition();
                    p.Latitude = Convert.ToDouble(positionReader["latitude"].ToString());
                    p.Longtitude = Convert.ToDouble(positionReader["longtitude"].ToString());
                    p.Name = positionReader["pname"].ToString();
                    p.ZoomLevel = Convert.ToInt32(positionReader["zoomlevel"].ToString());

                    result.Add(p);
                }
            }
            catch(Exception e)
            { 
            }

            return result;
        }

        [WebMethod]
        public List<CPosition> getHotList()
        {
            List<CPosition> result = new List<CPosition>();

            try
            {
                string DataServer = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=newtest;User ID=sa;Password=123456";
                SqlConnection con = new SqlConnection(DataServer);

                SqlCommand com = new SqlCommand();
                com.CommandType = System.Data.CommandType.Text;
                com.Connection = con;
                com.CommandText = "select * from " + hotTable ;

                con.Open();
                con.ChangeDatabase(dbname);
                SqlDataReader positionReader = com.ExecuteReader();

                while (positionReader.Read())
                {
                    CPosition p = new CPosition();
                    p.Latitude = Convert.ToDouble(positionReader["latitude"].ToString());
                    p.Longtitude = Convert.ToDouble(positionReader["longtitude"].ToString());
                    p.Name = positionReader["description"].ToString();
                    p.ZoomLevel = Convert.ToInt32(positionReader["zoomlevel"].ToString());

                    result.Add(p);
                }
            }
            catch (Exception e)
            {
            }

            return result;
        }
    }
}
